package com.gdufe.firesafe.dao;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.gdufe.firesafe.model.entity.InspectionEntity;
import com.gdufe.firesafe.model.vo.CurrentUserInspectionVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 安全检查记录表
 *
 * @author Liu
 * @email
 * @date 2024-03-26 16:34:06
 */
@Mapper
public interface InspectionDao extends BaseMapper<InspectionEntity> {
    //查询某个房屋最近的一条检查记录
    @Select("SELECT * FROM `inspection` WHERE house_id=#{houseId} ORDER BY `inspection_date` DESC LIMIT 1")
    InspectionEntity selectLastInspectionByHouseId(Integer houseId);

    //获取最近7次检查的日期
    @Select("SELECT `inspection_date` FROM `inspection` WHERE house_id=#{houseId} ORDER BY `inspection_date` DESC LIMIT 7")
    List<Date> get7Dates(Integer houseId);

    //获取最近7次检查的分数
    @Select("SELECT `result` FROM `inspection` WHERE house_id=#{houseId} ORDER BY `inspection_date` DESC LIMIT 7")
    List<Double> get7Points(Integer houseId);

    //该检查员每月的检查次数
    @Select("SELECT DATE_FORMAT(inspection_date, '%Y-%m') as `month`, COUNT(*) as `count` FROM `inspection` " +
            "WHERE `inspector_id`=#{inspectorId} GROUP BY `month` ORDER BY `month`")
    List<Map<String, Object>> getCountPerMonth(Integer inspectorId);

    //该检查员每月的平均分
    @Select("SELECT DATE_FORMAT(inspection_date, '%Y-%m') as `month`, AVG(`result`) as `avg` FROM `inspection` " +
            "WHERE `inspector_id`=#{inspectorId} GROUP BY `month` ORDER BY `month`")
    List<Map<String, Object>> getAvgScoresPerMonth(Integer inspectorId);

    @Select("SELECT DATE_FORMAT(inspection_date, '%Y-%m') as `month`,\n" +
            "       AVG(`result`) as `avg`,COUNT(*) as `count`\n" +
            "FROM `inspection` WHERE `inspector_id`=7 GROUP BY `month` ORDER BY `month`")
    List<Map<String, Object>> getAvgScoresAndCountPerMonth(Integer inspectorId);

    @Select("SELECT ion.id, ior.name as inspectorName, ion.inspection_date, h.address as houseLocation, " +
            "ion.result as score, ion.photos as photo " +
            "FROM `inspection` ion " +
            "LEFT JOIN `inspector` ior ON ion.inspector_id=ior.id " +
            "LEFT JOIN `house` h ON ion.house_id=h.id " +
            "${ew.customSqlSegment}")
    IPage<CurrentUserInspectionVO> getCurrentUserInspection(IPage<CurrentUserInspectionVO> page, @Param(Constants.WRAPPER) Wrapper<CurrentUserInspectionVO> wrapper);
}
